package com.rongji.cms.tools.temp.db;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.rongji.cms.tools.temp.db.entity.*;
import com.rongji.cms.tools.temp.db.utils.DbCompareUtil;
import com.rongji.cms.tools.temp.db.utils.DbUtil;
import com.rongji.cms.tools.temp.db.utils.MySQLUtil;

import java.io.*;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @author yang
 * @date 2023/1/28 11:50
 * @description
 */

public class DbTest {


    static String outTxtFile = "C:\\compareResult.txt";
    static String outExcelFile = "C:\\compareResult.xls";

    public static void main(String[] args) throws Exception {

        DbInfo dbAInfo = new DbInfo();
        dbAInfo.setDbName("orcl");
        dbAInfo.setDbHost("192.168.118.77:1521");
        dbAInfo.setDbUsername("rjcmscs");
        dbAInfo.setDbPassword("rjcmscs123.");


        DbInfo dbBInfo = new DbInfo();
        dbBInfo.setDbName("orcl");
        dbBInfo.setDbHost("192.168.118.77:1521");
        dbBInfo.setDbUsername("rjcmsdevelop");
        dbBInfo.setDbPassword("rjcmsdevelop");


//        DbInfo dbAInfo = new DbInfo();
//        dbAInfo.setDbName("test1");
//        dbAInfo.setDbHost("192.168.212.224:3306");
//        dbAInfo.setDbUsername("root");
//        dbAInfo.setDbPassword("123456");
//
//        DbInfo dbBInfo = new DbInfo();
//        dbBInfo.setDbName("test2");
//        dbBInfo.setDbHost("192.168.212.224:3306");
//        dbBInfo.setDbUsername("root");
//        dbBInfo.setDbPassword("123456");

        String dbType = "oracle";
//        String dbType = "mysql";

        Map<String, Map<String, OracleColumnProp>> dbAMap = DbUtil.getDBInfo(dbType, dbAInfo, OracleColumnProp.class);
        Map<String, Map<String, OracleColumnProp>> dbBMap = DbUtil.getDBInfo(dbType, dbBInfo, OracleColumnProp.class);
        String compareResult = DbCompareUtil.compareDBInfo(dbAMap, dbBMap, OracleColumnProp.class);
//        Map<String, Map<String, MysqlColumnProp>> dbAMap = DbUtil.getDBInfo(dbType, dbAInfo, MysqlColumnProp.class);
//        Map<String, Map<String, MysqlColumnProp>> dbBMap = DbUtil.getDBInfo(dbType, dbBInfo, MysqlColumnProp.class);
//        String compareResult = DbCompareUtil.compareDBInfo(dbAMap, dbBMap, MysqlColumnProp.class);
        System.out.println(compareResult);


        //        writeToTxt(compareResult);
        writeToExcel();


//        try {
//            Integer.parseInt("11");
//            "ssss".substring(1);
//
//
//            Method method1 = Test111.class.getMethod("m1", String.class);
//            String s1 = (String) method1.invoke(new Test111(), "111");
//            System.out.println("s1:" + s1);
//
//
//            Method method2 = Test111.class.getMethod("m2", String.class);
//            String s2 = (String) method2.invoke(null, "222");
//            System.out.println("s2:" + s2);
//
//        } catch (NoSuchMethodException e) {
//            e.printStackTrace();
//        } catch (InvocationTargetException e) {
//            e.printStackTrace();
//        } catch (IllegalAccessException e) {
//            e.printStackTrace();
//        }

    }


    /**
     * 将对比结果写入到txt
     *
     * @param compareResult
     */
    public static void writeToTxt(String compareResult) {
        try {
            //1、创建一个文件对象
            File txtFile = new File(outTxtFile);
            //2、判断文件是否存在，不存在则创建一个Excel文件
            if (!txtFile.exists()) {
                try {
                    txtFile.createNewFile();//创建一个新的文件
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            BufferedWriter out = new BufferedWriter(new FileWriter(txtFile));

            out.write(compareResult);
            out.flush();
            out.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("txt导出完成。。。。");
    }

    /**
     * 将对比结果导出到excel
     * 3个sheet写入到同一个excel文件
     */
    public static void writeToExcel() {
        //1、创建一个文件对象
        File excelFile = new File(outExcelFile);
        //2、判断文件是否存在，不存在则创建一个Excel文件
        if (!excelFile.exists()) {
            try {
                excelFile.createNewFile();//创建一个新的文件
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        ExcelWriter excelWriter = null;
        try {
            OutputStream os = new FileOutputStream(excelFile);

            excelWriter = EasyExcel.write(os).build();

//            缺少的表
            // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class 实际上可以一直变
            WriteSheet lackTableSheet = EasyExcel.writerSheet(0, "缺少的表").head(LackTableVo.class).build();
            excelWriter.write(DbCompareUtil.lackTableVoList, lackTableSheet);
//            缺少的字段
            WriteSheet lackColumnSheet = EasyExcel.writerSheet(1, "缺少的字段").head(LackColumnVo.class).build();
            excelWriter.write(DbCompareUtil.lackColumnVoList, lackColumnSheet);
//            差异的字段
            WriteSheet diffColumnSheet = EasyExcel.writerSheet(2, "有差异的字段").head(DiffColumnVo.class).build();
            excelWriter.write(DbCompareUtil.diffColumnVoList, diffColumnSheet);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }

            System.out.println("excel导出完成。。。。");
        }
    }


    public static Map<String, Map<String, Long>> compare() {
        Map<String, Map<String, Long>> tableMap = new ConcurrentHashMap<>();
        for (int i = 0; i < 5; i++) {
            Map<String, Long> dMap = new ConcurrentHashMap<>();
            dMap.put("" + i, new Long("111"));
            tableMap.put("" + i, dMap);
        }

        return tableMap;
    }


    public static <T> Map<String, Map<String, T>> compare1(Class<T> clz) {
        Map<String, Map<String, T>> tableMap = new ConcurrentHashMap<>();
        for (int i = 0; i < 5; i++) {
            Map<String, T> dMap = new ConcurrentHashMap<>();
            try {
                dMap.put("" + i, clz.newInstance());
                tableMap.put("" + i, dMap);
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

        }

        return tableMap;
    }


    public static <T> Map<String, Map<String, T>> getDBInfo(String host, String dbName, String userName, String userPwd, Class<T> clazz) throws Exception {
        Map<String, Map<String, T>> tableMap = new ConcurrentHashMap<>();
        Connection conn = null;
        Statement tableStatement = null;
        ResultSet tableResultSet = null;
        try {
            conn = MySQLUtil.getMySQLConnection(MySQLUtil.getConnectUrl(host, dbName), userName, userPwd);
            tableStatement = conn.createStatement();
            tableResultSet = tableStatement.executeQuery("");
            int tableColumnCount = tableResultSet.getMetaData().getColumnCount();
            while (tableResultSet.next()) {
                for (int i = 1; i <= tableColumnCount; i++) {
                    String tableName = tableResultSet.getString(i);
                    ResultSet columnResultSet = null;
                    Statement columnStatement = null;
                    try {
                        columnStatement = conn.createStatement();
                        columnResultSet = columnStatement.executeQuery(MySQLUtil.getColumnsSQL(dbName, tableName));

                        Method method = clazz.getMethod("getFromResult", ResultSet.class);
                        Map<String, T> columnsMap = (Map<String, T>) method.invoke(null, columnResultSet);


                        tableMap.put(tableName, columnsMap);
                    } finally {
                        MySQLUtil.closeConnection(columnResultSet, columnStatement, null);
                    }
                }
            }
        } finally {
            MySQLUtil.closeConnection(tableResultSet, tableStatement, conn);
        }
        return tableMap;
    }
}
